<?php

namespace app\models;

use Yii;


class Statistics extends \yii\base\Model
{



    public static function count_query($sql)
    {
        $rs = Db::sql($sql);
        if ($rs != null && count($rs) > 0) {
            return $rs[0]['numc'];
        }
        return 0;
    }

    //时间段,数据统计
    public static function get_between_statistic($month_start = '2021-12-31', $month_end = '2021-12-31')
    {
        $total = [];
        $month_start = date('Y-m-d', strtotime($month_start)) . ' 00:00:00';
        $month_end = date('Y-m-d', strtotime($month_end)) . ' 23:59:59';

        $sql_create = "select count(id) as numc from yyh_patient where createtime between '{$month_start}' and '{$month_end}' ";
        $total['create'] = self::count_query($sql_create);

        $sql_order = "select count(id) as numc from yyh_patient where createtime between '{$month_start}' and '{$month_end}' and order_time > '2020-01-01' ";
        $total['order'] = self::count_query($sql_order);

        $sql_modify = "select count(id) as numc from yyh_patient where createtime between '{$month_start}' and '{$month_end}' and status in ('已改约', '流失') ";
        $total['cancel_modify'] = self::count_query($sql_modify);

        $sql_should = "select count(id) as numc from yyh_patient where order_time between '{$month_start}' and '{$month_end}' ";
        $total['should_arrive'] = self::count_query($sql_should);

        $sql_arrive = "select count(id) as numc from yyh_patient where arrive_time between '{$month_start}' and '{$month_end}' ";
        $total['arrive'] = self::count_query($sql_arrive);

        return $total;
    }

    //月,数据统计
    //$month_value = '2021-12'
    public static function get_month_statistic($month_value)
    {
        $timestamp = strtotime($month_value);
        $start_time = date('Y-m-01', $timestamp);
        $mdays = date('t', $timestamp);
        $end_time = date('Y-m-' . $mdays, $timestamp);
        return self::get_between_statistic($start_time, $end_time);
    }

    //天,数据统计
    public static function get_days_statistic($month_start = '2021-12-31', $month_end = '2021-12-31')
    {
        $total = [];

        $startTime = strtotime($month_start);
        $endTime = strtotime($month_end);
        $middle = $startTime;
        while ($middle <= $endTime) {
            $day_name = trim(date('Y-m-d', $middle) . PHP_EOL);
            $total[$day_name] = ['create' => 0, 'order' => 0, 'arrive' => 0, 'cancel_modify' => 0, 'should_arrive' => 0];
            $middle = strtotime('+1 day', $middle);
        }

        $month_start = date('Y-m-d', $startTime) . ' 00:00:00';
        $month_end = date('Y-m-d', $endTime) . ' 23:59:59';

        $sql_create = "select date(createtime) as create_date, count(id) as numc from yyh_patient where createtime between '{$month_start}' and '{$month_end}' group by create_date";
        $rs_create = Db::sql($sql_create);
        foreach ($rs_create as $v) {
            $total[$v['create_date']]['create'] = $v['numc'];
        }

        $sql_order = "select date(createtime) as create_date, count(id) as numc from yyh_patient where createtime between '{$month_start}' and '{$month_end}' and order_time > '2020-01-01' group by create_date";
        $rs_order = Db::sql($sql_order);
        foreach ($rs_order as $v) {
            $total[$v['create_date']]['order'] = $v['numc'];
        }

        $sql_modify = "select date(createtime) as create_date, count(id) as numc from yyh_patient where createtime between '{$month_start}' and '{$month_end}' and status in ('已改约', '流失') group by create_date";
        $rs_modify = Db::sql($sql_modify);
        foreach ($rs_modify as $v) {
            $total[$v['create_date']]['cancel_modify'] = $v['numc'];
        }

        $sql_should = "select date(order_time) as order_date, count(id) as numc from yyh_patient where order_time between '{$month_start}' and '{$month_end}' group by order_date";
        $rs_should = Db::sql($sql_should);
        foreach ($rs_should as $v) {
            $total[$v['order_date']]['should_arrive'] = $v['numc'];
        }

        $sql_arrive = "select date(arrive_time) as arrive_date, count(id) as numc from yyh_patient where arrive_time between '{$month_start}' and '{$month_end}' group by arrive_date";
        $rs_arrive = Db::sql($sql_arrive);
        foreach ($rs_arrive as $v) {
            $total[$v['arrive_date']]['arrive'] = $v['numc'];
        }

        return $total;
    }

    public static function get_workgroup_month_data($month = '2021-12', $user_where = '')
    {
        $sheet = [];
        $res = [];

        $sql_create = "select user_id, count(id) as numc from yyh_patient where createtime like '{$month}%' group by user_id";
        $rs_create = Db::sql($sql_create);
        if ($rs_create != null && count($rs_create) > 0) {
            foreach ($rs_create as $v) {
                $sheet[$v['user_id']]['create'] = $v['numc'];
            }
        }

        $sql_order = "select user_id, count(id) as numc from yyh_patient where createtime like '{$month}%' and order_time > '2020-01-01' group by user_id";
        $rs_order = Db::sql($sql_order);
        if ($rs_order != null && count($rs_order) > 0) {
            foreach ($rs_order as $v) {
                $sheet[$v['user_id']]['order'] = $v['numc'];
            }
        }

        $sql_arrive = "select user_id, count(id) as numc from yyh_patient where arrive_time like '{$month}%' group by user_id";
        $rs_arrive = Db::sql($sql_arrive);
        if ($rs_arrive != null && count($rs_arrive) > 0) {
            foreach ($rs_arrive as $v) {
                $sheet[$v['user_id']]['arrive'] = $v['numc'];
            }
        }

        $previous_timestamp = strtotime(date('Y-m', strtotime($month . ' - 1 month')));
        $previous_start_time = date('Y-m-01 00:00:00', $previous_timestamp);
        $previous_mdays = date('t', $previous_timestamp);
        $previous_end_time = date('Y-m-' . $previous_mdays . ' 23:59:59', $previous_timestamp);
        if ($month == date('Y-m')) {
            $previous_end_time = date('Y-m-d H:i:s', strtotime('- 1 month'));
        }
        $sql_previous = "select user_id, count(id) as numc from yyh_patient where arrive_time between '{$previous_start_time}' and '{$previous_end_time}' group by user_id";
        $rs_previous = Db::sql($sql_previous);
        if ($rs_previous != null && count($rs_previous) > 0) {
            foreach ($rs_previous as $v) {
                $sheet[$v['user_id']]['previous'] = $v['numc'];
            }
        }

        $users_task = Task::find()->where("yearmonth like '{$month}%'")->all();
        if ($users_task != null && count($users_task) > 0) {
            foreach ($users_task as $v) {
                $sheet[$v['user_id']]['task'] = $v['task_num'];
            }
        }

        foreach ($sheet as $user_id => $data) {
            if (null != $data['arrive'] && (int)$data['arrive'] > 0 && null != $data['task_num'] && (int)$data['task_num'] > 0) {
                $sheet[$user_id]['per'] = number_format((int)$data['arrive'] * 100 / (int)$data['task_num'], 2);
            } else {
                $sheet[$user_id]['per'] = 0;
            }
        }

        $user_list = User::find()->where($user_where)->orderBy('workgroup, id')->all();
        if ($user_list == null || count($user_list) < 1) {
            return false;
        }
        foreach ($user_list as $u) {
            if ($res[$u->workgroup] == null) {
                $res[$u->workgroup] = [];
            }
            $data = ['user_id' => $u->id, 'name' => $u->showname];
            if (null != $sheet[$u->id]) {
                $data['sheet'] = $sheet[$u->id];
            }
            $res[$u->workgroup][] = $data;
        }

        return $res;
    }

    public static function get_jobs_data($month_start, $month_end)
    {
        $jobname = [];
        $sheet = [];
        $month_start = date('Y-m-d', strtotime($month_start)) . ' 00:00:00';
        $month_end = date('Y-m-d', strtotime($month_end)) . ' 23:59:59';

        $user_list = User::find()->orderBy('workgroup')->all();
        if (null != $user_list && count($user_list) > 0) {
            foreach ($user_list as $j) {
                $jobname[$j->id] = $j->workgroup;
            }
        }

        $sql_create = "select user_id, count(id) as numc from yyh_patient where createtime  between '{$month_start}' and '{$month_end}' group by user_id";
        $rs_create = Db::sql($sql_create);
        if ($rs_create != null && count($rs_create) > 0) {
            foreach ($rs_create as $v) {
                if (null == $sheet['create'][$jobname[$v['user_id']]]) {
                    $sheet['create'][$jobname[$v['user_id']]] = 0;
                }
                $sheet['create'][$jobname[$v['user_id']]] += $v['numc'];
            }
        }

        $sql_order = "select user_id, count(id) as numc from yyh_patient where createtime  between '{$month_start}' and '{$month_end}' and order_time > '2020-01-01' group by user_id";
        $rs_order = Db::sql($sql_order);
        if (null != $rs_order && count($rs_order) > 0) {
            foreach ($rs_order as $v) {
                if (null == $sheet['order'][$jobname[$v['user_id']]]) {
                    $sheet['order'][$jobname[$v['user_id']]] = 0;
                }
                $sheet['order'][$jobname[$v['user_id']]] += $v['numc'];
            }
        }

        $sql_arrive = "select user_id, count(id) as numc from yyh_patient where arrive_time  between '{$month_start}' and '{$month_end}' group by user_id";
        $rs_arrive = Db::sql($sql_arrive);
        if ($rs_arrive != null && count($rs_arrive) > 0) {
            foreach ($rs_arrive as $v) {
                if (null == $sheet['arrive'][$jobname[$v['user_id']]]) {
                    $sheet['arrive'][$jobname[$v['user_id']]] = 0;
                }
                $sheet['arrive'][$jobname[$v['user_id']]] += $v['numc'];
            }
        }

        return $sheet;
    }
}
